%macro get_extra_deps;


	
	proc printto log = "&logdir/get_extra_deps.txt" new;
	run;


		data _null_;
			set rlib.nonfilers;
			file "&tmpdir./tinlist" dlm=',';
			put prim_tin sec_tin; 
		run;
		


	proc sql noprint;
		[connection details redacted]
			create table sample as
			select * from connection to iq 
		(
		
		/* cases when 2019 prim_tin is the claimer */
		select distinct
			a.prim_tin,
			a.sec_tin,
			b.[redacted],
			c.[redacted] as dep_ssn,
			floor(d.[redacted]/10000) as dep_yob
		into #prim
		from #tinlist as a 
			inner join [redacted] as b 
				on(a.prim_tin = b.[redacted])
			inner join [redacted] as c 
				on(b.[redacted] = c.[redacted])
			inner join dm1 as d 
				on(c.[redacted] = d.[redacted])
			inner join irtf_f1040 as e
				on(b.[redacted] = e.[redacted] and b.[redacted] = e.[redacted])
		where (e.[redacted] - &base_yr = 0
						or
				(e.[redacted] - &base_yr = 1 
					and e.[redacted] = 1 
					and e.[redacted] = 1
					and e.[redacted] = 1)
		)
			and b.[redacted] in("01","02","03","04","05","06","07")
			and d.[redacted] <=&base_yr.1231
			and b.[redacted] > 0
			
	/* cases when 2019 sec_tin is the claimer */
		select distinct
			a.prim_tin,
			a.sec_tin,
			b.[redacted],
			c.[redacted] as dep_ssn,
			floor(d.[redacted]/10000) as dep_yob
		into #sec
		from #tinlist as a 
			inner join [redacted] as b 
				on(a.sec_tin = b.[redacted])
			inner join [redacted] as c 
				on(b.[redacted] = c.[redacted])
			inner join [redacted] as d 
				on(c.[redacted] = d.[redacted])
			inner join [redacted] as e
				on(b.[redacted] = e.[redacted] and b.[redacted] = e.[redacted])
		where (e.[redacted] - &base_yr = 0
						or
				(e.[redacted] - &base_yr = 1 
					and e.[redacted] = 1 
					and e.[redacted] = 1
					and e.[redacted] = 1)
		)
			and b.[redacted] in("01","02","03","04","05","06","07")
			and d.[redacted] <=&base_yr.1231
			and b.[redacted] > 0	
			and a.sec_tin > 0
			
		/* take the union */
		select
			prim_tin, sec_tin, [redacted], dep_ssn, dep_yob
		into #t1
		from #prim
		union
		select
			prim_tin, sec_tin, [redacted], dep_ssn, dep_yob
		from #sec
			
			
			
			
			
		/* make sure not claimed by someone else in 2019 */
		select distinct
			a.prim_tin,
			a.dep_ssn,
			a.dep_yob
		from #t1 as a
			left outer join [redacted] as b
				on(b.[redacted] = &base_yr 
					and a.[redacted] = b.[redacted])	
		where b.[redacted] is null
			or a.prim_tin = b.[redacted]
			or a.sec_tin = b.[redacted]
		order by a.prim_tin,
			a.dep_ssn
			
		
	);
	disconnect from iq;
	quit;

			
	/* hit against rlib.deps */
	proc sort data = rlib.deps;
		by prim_tin dep_ssn;
	run;
	
	data extra_deps;
		merge rlib.deps(in=a) sample(in=b);
		by prim_tin dep_ssn;
		if a = 0 & b = 1;
		u13 = dep_yob > &base_yr-12;
		u17 = dep_yob > &base_yr-17;
		u18 = dep_yob > &base_yr-18;
		keep prim_tin dep_yob u13 u17 u18;
	run;
	
	
	proc means data = extra_deps nway noprint;
		class prim_tin;
		output out = rlib.extra_deps(drop=_type_ _freq_)
			n(prim_tin) = extra_deps
			sum(u13 u17 u18) = extra_deps13 extra_deps17 extra_deps18;
	run;
	


	proc printto;
	run;

%mend;